import psycopg2
import json


def select_form_db(db, sql):
    try:
        cursor = db.cursor()
        cursor.execute(sql)
        return cursor.fetchall()
    except Exception as e:
        print(e)
        print("查询失败！！")
    finally:
        cursor.close()


def insert_into_db(db, sql):
    try:
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
        print("插入成功")
    except Exception as e:
        print(e)
        db.rollback()
        print("插入失败！！")
    finally:
        cursor.close()


if __name__ == "__main__":
    # 打开testdb.json文件，并将其内容加载到testdb_config变量中
    with open("testdb.json", "r") as file:
        testdb_config = json.load(file)

    # 查询
    sql_select = 'SELECT * FROM "workman"'
    try:
        db = psycopg2.connect(**testdb_config)
        results = select_form_db(db, sql_select)
        for row in results:
            print(row)
    except Exception as e:
        print(e)
    finally:
        db.close()
        print("连接关闭！！")

    # 插入
    sql_insert = """
        insert into workman(sname, duties, age, phone, adds) values('于小A', '巡特文职辅警', 35, 13514242777, '大众') # 必须是单引号
    """
    try:
        db = psycopg2.connect(**testdb_config)
        insert_into_db(db, sql_insert)
        results = select_form_db(db, sql_select)
        for row in results:
            print(row)
    except Exception as e:
        print(e)
    finally:
        db.close()
        print("连接关闭！！")


# 注释掉的代码，连接到数据库
# db = psycopg2.connect(
#     host="192.168.122.205", database="testdb", user="postgres", password="A123#"
# )

# # 然后使用**data将字典的键值对作为单独的关键字参数传递给函数
# db = psycopg2.connect(**testdb_config)

# print("连接成功！！")
# cursor = db.cursor()


# sql = 'SELECT * FROM "workman"'

# try:
#     cursor.execute(sql)
#     rows = cursor.fetchall()
#     for row in rows:
#         print(row)
# except Exception as e:
#     print(e)
# finally:
#     cursor.close()
#     db.close()
#     print("连接关闭！！")


# sql = """
#     insert into workman(sname, duties, age, phone, adds) values(%s,%s,%s,%s,%s)
# """
# args = ("于箫通", "巡特文职辅警", 35, 13514242777, "大众")


# try:
#     执行sql语句
#     cursor.execute(sql, args)  # 插入单条记录
#     提交事务
#     db.commit()
#     print("插入成功")
# except Exception as e:
#     print(e)
#     如果出现异常，回滚
#     db.rollback()
#     print("插入失败")
# finally:
#     关闭数据库连接
#     db.close()
